﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Reflection;

namespace StudentReport.Logic
{
    public class ExcelAccess
    {

        private string filePath = String.Empty;


        public ExcelAccess(string filePath)
        {
            this.filePath = filePath;
        }

        public DataTable SelectClassResults(string workSheet)
        {
            DataTable results = new DataTable();
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
                //Term1
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + workSheet + "$]", MyConnection);

                MyCommand.TableMappings.Add("Table", "Table");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                results = DtSet.Tables[0];

                MyConnection.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return results;
        }

        public string[] GetClassInfo()
        {
            string[] classInfo = new string[7];

            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);


            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            classInfo[0] = xlWorkSheet.get_Range("B1", "B1").Value2.ToString() ?? String.Empty;
            classInfo[1] = xlWorkSheet.get_Range("B2", "B2").Value2.ToString() ?? String.Empty;
            classInfo[2] = xlWorkSheet.get_Range("B3", "B3").Value2.ToString() ?? String.Empty;
            classInfo[3] = xlWorkSheet.get_Range("B4", "B4").Value2.ToString() ?? String.Empty;
            classInfo[4] = xlWorkSheet.get_Range("B5", "B5").Value2.ToString() ?? String.Empty;
            classInfo[5] = xlWorkSheet.get_Range("B6", "B6").Value2.ToString() ?? String.Empty;
            classInfo[6] = xlWorkSheet.get_Range("B7", "B7").Value2.ToString() ?? String.Empty;
            xlWorkBook.Close(false, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            return classInfo;
        }

        public DataTable SelectAverageResults(string type, string subject)
        {
            DataTable results = new DataTable();
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");

                //first
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select AVG([" + subject + "]) from [" + type + "1$]", MyConnection);

                MyCommand.TableMappings.Add("Table1", "Table1");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);

                //Other 
                for (int i = 2; i <= Convert.ToInt32(type.Equals("Term") ? Properties.Settings.Default.NumberOfTerms : Properties.Settings.Default.NumberOfUnits); i++)
                {
                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select  AVG([" + subject + "]) from [" + type + i + "$]", MyConnection);

                    MyCommand.TableMappings.Add("Table" + i, "Table" + i);
                    MyCommand.Fill(DtSet);
                }

                results = DtSet.Tables[0];

                MyConnection.Close();
            }
            catch (Exception)
            {
                throw new Exception("Document format is invalid or some required data is missing in the document "); ;
            }
            return results;

        }

        public DataTable SelectResults(string admitionNo, string type)
        {

            DataTable results = new DataTable();
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");

                //first
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + type + "1$] where [" +
                    Constants.AdmitionNoHeader + "] = " + admitionNo, MyConnection);

                MyCommand.TableMappings.Add("Table1", "Table1");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);

                DataColumn column = DtSet.Tables[0].Columns[Constants.AdmitionNoHeader];
                column.ColumnName = type;
                DtSet.Tables[0].Rows[0].SetField(column, 1);


                DtSet.Tables[0].Columns.Remove(Constants.StudentNoHeader);
                DtSet.Tables[0].Columns.Remove(Constants.StudentNameHeader);

                //Other 
                for (int i = 2; i <= Convert.ToInt32(type.Equals("Term") ? Properties.Settings.Default.NumberOfTerms : Properties.Settings.Default.NumberOfUnits); i++)
                {
                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + type + i + "$] where [" +
                        Constants.AdmitionNoHeader + "] = " + admitionNo, MyConnection);

                    MyCommand.TableMappings.Add("Table" + i, "Table" + i);
                    MyCommand.Fill(DtSet);


                    DtSet.Tables[0].Rows[i - 1].SetField(column, i);

                    // DtSet.Tables[0].Columns.Remove(Constants.AdmitionNoHeader);
                    DtSet.Tables[0].Columns.Remove(Constants.StudentNoHeader);
                    DtSet.Tables[0].Columns.Remove(Constants.StudentNameHeader);

                }

                results = DtSet.Tables[0];


                MyConnection.Close();
            }
            catch (Exception)
            {
                throw new Exception("Document format is invalid or some required data is missing in the document "); ;
            }
            return results;


        }

        public DataTable SearchStudent(string searchInput)
        {

            DataTable results = new DataTable();
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
                //Term1
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select [" +
                    Constants.AdmitionNoHeader + "], [" +
                    Constants.StudentNameHeader + "] from [Term1$] where [" +
                    Constants.StudentNameHeader + "] Like '%" + searchInput + "%' OR [" +
                    Constants.AdmitionNoHeader + "] Like '%" + searchInput + "%'", MyConnection);


                MyCommand.TableMappings.Add("TableTerm1", "TableT1");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                results = DtSet.Tables[0];

                MyConnection.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return results;
        }



        public void OpenExcel()
        {

            Microsoft.Office.Interop.Excel.Application objexcel;


            objexcel = new Microsoft.Office.Interop.Excel.Application();
            FileInfo fi = new FileInfo(filePath);
            if (fi.Exists)
            {
                objexcel.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


                objexcel.Visible = true;
            }
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}
